﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using InsideDL.MISControl;
using InsideDL.MISData;


namespace InsideDL
{
    public partial class frmIR_Investment : MISControl.frmMatrix 
    {
        protected InsideDL.MISService.EXLAPP exlAPP = null;
        public frmIR_Investment()
        {
            InitializeComponent();
            this.controlList = new List<ucSelFileOrPath>
            {
                ucAccount   
            };
            ucExecute1.ExecuteClick += new EventHandler(ucClickFacade);
            ucExecute_Exit.ExecuteClick += new EventHandler(ucClickFacade);
        }

        protected override void execute()
        {
            if (base.Vaildation().Equals(false))
            { return; }
            exlAPP = new InsideDL.MISService.EXLAPP();
            DataTable newTB = GetNewTBTable(); //空
            DataTable tb = executeTB(); //會計來源
            DataTable code = executeCode(); //只有code
            exlAPP.CloseFile();
            exlAPP = new InsideDL.MISService.EXLAPP();
            exlAPP.OpenFile(ucTemplate.Txt);
            string[] tbColumns = {"A","B","C","D","E","F","G","H","I","J"};
            int iRow = 2;
            foreach (DataRow row in code.Rows)
            {
                IQueryable<DataRow> query = (from acc in tb.AsEnumerable()
                    where acc.Field<string>("會計科目").ToString().Equals(
                    row["會計科目"].ToString())
                    select acc).AsQueryable();
                DataRow newTBRow = newTB.NewRow();

                foreach (DataRow seleRow in query)
                {
                    foreach (DataColumn col in code.Columns)
                    {
                        newTBRow[col.ColumnName] = row[col.ColumnName];
                    }
                    newTBRow["餘額G"] = seleRow["借方餘額"];
                    newTBRow["餘額H"] = seleRow["貸方餘額"];
                    newTB.Rows.Add(newTBRow);
                    for(int i=0;i<tbColumns.Length;i++)
                    {
                        exlAPP.SetRangeValue("會計師調整完TB"
                            ,string.Format("{0}{1}",tbColumns[i],iRow.ToString())
                            ,newTBRow[i].ToString());
                    }
                    iRow++;
                }                
            }
            exlAPP.SaveCloseXLS();
        }

        /// <summary>
        /// 取得code sheet 的資料
        /// </summary>
        protected DataTable executeCode()
        {
            DataIR_Investment di = new DataIR_Investment();
            DataTable dt = di.GetCodeData(ucTemplate.Txt);
            return dt;
        }

        protected DataTable executeTB()
        {
            exlAPP.OpenFile(ucAccount.Txt);
            string tbSht, rng, val;
            tbSht = "會計師調完之TB";
            DataTable dt = getDataTable();
            string[] columns = { "A", "B", "G", "H" };
            int currentPos, nextColumn, loop, endColumn;
            endColumn = 4137;
            currentPos = 12;
            loop = 43;
            nextColumn = 11;

            while (currentPos < endColumn)
            {
                for (int i = 0; i < loop; i++)
                {
                    DataRow row = dt.NewRow();
                    for (int x = 0; x < columns.Length; x++)
                    {
                        rng = string.Format("{0}{1}",
                            columns[x], currentPos.ToString());
                        val = exlAPP.GetSheetRangeValue(tbSht, rng);
                        row[x] = val;
                    }
                    dt.Rows.Add(row);
                    currentPos++;
                }
                currentPos += nextColumn;
            }
            return dt;
        }

        System.Data.DataTable getDataTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("會計科目");
            dt.Columns.Add("科目中文名稱");
            dt.Columns.Add("借方餘額");
            dt.Columns.Add("貸方餘額");
            return dt;
        }

        public DataTable GetNewTBTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("會計科目");
            dt.Columns.Add("科目中文名稱");
            dt.Columns.Add("餘額G");
            dt.Columns.Add("餘額H");
            dt.Columns.Add("投資YN");
            dt.Columns.Add("AS4001");
            dt.Columns.Add("依34號公報");
            dt.Columns.Add("地區");
            dt.Columns.Add("投資標的");
            dt.Columns.Add("成本或評價");
            return dt;
        }
    }
}
